MYDB=$DB_CAR_COSTS
INPUT_FOLDER=../data/raw_data/car_costs
INPUT_GEO_FOLDER=../data/raw_data/geography
OUTPUT_FOLDER=../data/data_csv

##### Read input #####
sqlite3 $MYDB "CREATE TABLE tmp0( \
    year INTEGER,
    RegionId INTEGER, \
    Region TEXT, \
    TotalBudgetAnnuel INTEGER, \
    AchatReprise INTEGER, \
    FraisFinanciers INTEGER, \
    Assurance INTEGER, \
    Carburant INTEGER, \
    Entretien INTEGER, \
    GarageVehicule INTEGER, \
    Peage INTEGER, \
    KilometrageAnnuel INTEGER, \
    PrixAuKm REAL \
);"
for YEAR in {2010..2017} ; do
    echo $YEAR
    importCSVFile $MYDB $INPUT_FOLDER/AutomobileClub/${YEAR}_data.csv tmp
    sqlite3 $MYDB "create table tmp2 as select $YEAR as year, a.* from tmp a;"
    sqlite3 $MYDB "insert into tmp0 select * from tmp2;"
    dropTables $MYDB tmp tmp2
done

# Merge RegionId with DEPs (this changes over time)
importCSVFile $MYDB $INPUT_GEO_FOLDER/depts2012.csv dep1
importCSVFile $MYDB $INPUT_GEO_FOLDER/depts2018.csv dep2

sqlite3 $MYDB "create table years1 as select distinct year from tmp0 where year < 2017;"
sqlite3 $MYDB "create table years2 as select distinct year from tmp0 where year >= 2017;"

sqlite3 $MYDB "create table deps2region as select * from years1, dep1;"
sqlite3 $MYDB "insert into deps2region select * from years2, dep2"

### Get data at departement-year level
equalJoin $MYDB 1 tmp1 tmp0 deps2region year RegionId
selectColumns $MYDB 1 mydata tmp1 year DEP TotalBudgetAnnuel AchatReprise \
    FraisFinanciers Assurance Carburant Entretien GarageVehicule Peage KilometrageAnnuel PrixAuKm
dropTables $MYDB tmp0 tmp1 dep1 dep2 deps2region years1 years2

### Get data at commune-year level
equalJoin $MYDB 1 mydata2 mycommunes mydata DEP
selectColumns $MYDB 1 mydata3 mydata2 CodeInsee year CantonId DEP RegionId TotalBudgetAnnuel AchatReprise \
    FraisFinanciers Assurance Carburant Entretien GarageVehicule Peage KilometrageAnnuel PrixAuKm
dropTables $MYDB mydata mydata2
renameTable $MYDB mydata3 mydata_year

### Get data at commune-week level
sqlite3 $MYDB "create table mydata_week as \
    select CodeInsee, weekNum, CantonId, DEP, RegionId, \
        TotalBudgetAnnuel, AchatReprise, FraisFinanciers, Assurance, Carburant, Entretien, GarageVehicule, \
        Peage, KilometrageAnnuel, PrixAuKm \
    from mydata_year a, myweeks b where a.year = b.year;"

### Get data at commune-day level
sqlite3 $MYDB "create table mydata_day as \
    select CodeInsee, dayNum, CantonId, DEP, RegionId, \
        TotalBudgetAnnuel, AchatReprise, FraisFinanciers, Assurance, Carburant, Entretien, GarageVehicule, \
        Peage, KilometrageAnnuel, PrixAuKm \
    from mydata_year a, mydays b where a.year = b.year;"

# Aggregate to canton level
for TIME_LEVEL in week day ; do
    echo "CantonId - ${TIME_LEVEL}..." 
    if [ $TIME_LEVEL == "day" ]; then TIME_ID=dayNum; fi
    if [ $TIME_LEVEL == "week" ]; then TIME_ID=weekNum; fi

    selectDistinct $MYDB 1 tmp_CantonId_$TIME_LEVEL mydata_$TIME_LEVEL CantonId \
        $TIME_ID TotalBudgetAnnuel AchatReprise FraisFinanciers Assurance Carburant Entretien \
        GarageVehicule Peage KilometrageAnnuel PrixAuKm
    
    # Create an index to speed up merging with Scope
    sqlite3 $MYDB "create index if not exists index_tmp_CantonId_$TIME_LEVEL ON tmp_CantonId_$TIME_LEVEL (CantonId, $TIME_ID);"
    
    # Merge with scope
    sqlite3 $MYDB "create table AutomobileCosts_CantonId_$TIME_LEVEL as \
        select a.CantonId, a.$TIME_ID, TotalBudgetAnnuel, AchatReprise, FraisFinanciers, \
            Assurance, Carburant, Entretien, \
            GarageVehicule, Peage, KilometrageAnnuel, PrixAuKm \
        from Scope_CantonId_$TIME_LEVEL a left join tmp_CantonId_$TIME_LEVEL b \
        on a.CantonId = b.CantonId and a.$TIME_ID = b.$TIME_ID"
    
    dropTables $MYDB tmp_CantonId_$TIME_LEVEL
done
# Comment: there would be issues with regions, because the definition of regions has changed for the 2017 data.

### Clean up!
dropTables $MYDB mydata_year mydata_week mydata_day
for TIME_LEVEL in week day ; do
    dropTables $MYDB Scope_CantonId_${TIME_LEVEL}
done
dropTables $MYDB mycommunes mydays myweeks
sqlite3 $MYDB "VACUUM;"
